﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace AutoSendMailFromExecl
{
    /// <summary>
    /// 读取一堆 execl 文件的 每一个execl的第一个sheet
    /// </summary>
    public class ExeclDatas
    {
        /// <summary>
        /// 存储类型和文件名对应的
        /// </summary>
        public Dictionary<string, HashSet<string>> SheetName_And_FileName = new Dictionary<string, HashSet<string>>();

        /// <summary>
        /// 存储类型和文件名对应的
        /// </summary>
        public Dictionary<string, HashSet<string>> FileName_And_SheetName = new Dictionary<string, HashSet<string>>();

        /// <summary>
        ///  key：  excel 文件名
        ///  <para>value： excel 文件对应的数据</para>
        /// </summary>
        public Dictionary<string, ExeclData> Datas = new Dictionary<string, ExeclData>();

        static public ExeclDatas ReadExcelDataToCache(List<string> excelPaths, Action<string> show, bool isHebing = false)
        {

            if (excelPaths == null || excelPaths.Count == 0)
            {
                return null;
            }

            Dictionary<string, HashSet<string>> filename_and_key = new Dictionary<string, HashSet<string>>();

            ExeclDatas exceldatas = new ExeclDatas();

            foreach (var excelPath in excelPaths)
            {
                ExeclData exceldata = new ExeclData();

                exceldata.Read(excelPath, show);

                string datakey = Path.GetFileNameWithoutExtension(excelPath);
                if (!exceldatas.Datas.ContainsKey(datakey))
                {
                    //存储数据
                    exceldatas.Datas.Add(datakey, exceldata);
                }
                else
                {
                    foreach (var row in exceldata.Rows)
                    {
                        if (exceldatas.Datas[datakey].Rows.ContainsKey(row.Value.AtKey))
                        {
                            show("文件：" + Path.GetFileName(excelPath) + " 存在重复主键 " + row.Value.AtKey);
                            return null;
                        }
                        exceldatas.Datas[datakey].Rows.Add(row.Value.AtKey, row.Value);
                    }
                }
                {
                    //存储模型
                    if (!exceldatas.SheetName_And_FileName.ContainsKey(exceldata.SheetName))
                    {
                        exceldatas.SheetName_And_FileName.Add(exceldata.SheetName, new HashSet<string>());
                    }

                    exceldatas.SheetName_And_FileName[exceldata.SheetName].Add(datakey);

                    //存储模型
                    if (!exceldatas.FileName_And_SheetName.ContainsKey(datakey))
                    {
                        exceldatas.FileName_And_SheetName.Add(datakey, new HashSet<string>());
                    }
                    exceldatas.FileName_And_SheetName[datakey].Add(exceldata.SheetName);
                }
                if (!exceldata.SheetName.Equals(datakey))
                {
                    {
                        //存储数据
                        if (!exceldatas.Datas.ContainsKey(exceldata.SheetName))
                        {
                            exceldatas.Datas[exceldata.SheetName] = new ExeclData() { SheetName = exceldata.SheetName };
                        }
                        foreach (var row in exceldata.Rows)
                        {
                            if (exceldatas.Datas[exceldata.SheetName].Rows.ContainsKey(row.Key))
                            {
                                show("文件：" + Path.GetFileName(excelPath) + " 存在重复主键 " + row.Key);
                                return null;
                            }
                            exceldatas.Datas[exceldata.SheetName].Rows.Add(row.Key, row.Value);
                        }
                    }
                }
            }
            return exceldatas;
        }
    }

    /// <summary>
    ///  单个 execl 文件的第一个sheet
    /// </summary>
    public class ExeclData
    {
        public string SheetName = null;

        /// <summary>
        /// 数据行,key 为数据的主键
        /// </summary>
        public Dictionary<String, ExeclDataRow> Rows = new Dictionary<String, ExeclDataRow>();

        String ReplaceSideLine(String a)
        {
            if (string.IsNullOrWhiteSpace(a)) { return ""; }
            else
            {
                if (a.ToLower() == "class")
                {
                    a = "Clazz";
                }
                return a.Replace("-", "_");
            }
        }

        /// <summary>
        /// 根据excel 解析数据类型
        /// </summary>
        /// <param name="title">列头</param>
        /// <param name="notes">注释</param>
        /// <param name="data">数据</param>
        /// <param name="row">返回值</param>
        /// <returns></returns>
        bool GetExcelPropertyType(ICell title, ICell notes, ICell data, ref ExeclData.ExeclDataRow row)
        {
            if (title == null || string.IsNullOrWhiteSpace(title.ToString()))
            {
                return false;
            }

            string property = title.StringCellValue;
            string[] types = property.Split('=');
            property = property.ToUpper();
            if (property.IndexOf("=HL") >= 0)
            {
                return false;
            }

            ExeclData.ExeclDataRow.ExeclDataCell cell = new ExeclData.ExeclDataRow.ExeclDataCell();
            cell.CellName = ReplaceSideLine(types[0]).TrimStart(Convert.ToChar(" ")).TrimEnd(Convert.ToChar(" "));
            cell.CellNotes = notes == null ? "" : notes.ToString().Replace("\n", "").Replace("\r", "");
            //查找关联的文件名  
            if (property.IndexOf("=FF_") >= 0)
            {
                foreach (string item in types)
                {
                    if (item.ToUpper().IndexOf("FF_") == 0)
                    {
                        //表示存在关联数据
                        cell.CellFileName = item.Substring(3);
                        cell.CellFileName = this.ReplaceSideLine(cell.CellFileName);
                        break;
                    }
                }
            }

            if (property.IndexOf("=B") >= 0)
            {
                //bool
                cell.CellValueType = "Boolean";
            }
            else if (property.IndexOf("=D") >= 0)
            {
                //double
                cell.CellValueType = "double";
            }
            else if (property.IndexOf("=S") >= 0)
            {
                cell.CellValueType = "String";
                if (property.IndexOf("=S_") >= 0)
                {
                    foreach (string item in types)
                    {
                        if (item.ToUpper().IndexOf("S_") == 0)
                        {
                            //表示存在字符长度指定
                            string strLen = item.Substring(2);
                            int len;
                            if (int.TryParse(strLen, out len))
                            {
                                cell.CellLength = len;
                            }
                            break;
                        }
                    }
                }
                if (cell.CellLength == 0)
                {
                    cell.CellLength = 255;
                }
            }
            else if (property.IndexOf("=FL") >= 0)
            {
                cell.CellValueType = "float";
            }
            else if (property.IndexOf("=L") >= 0)
            {
                cell.CellValueType = "long";
            }
            else
            {
                //int
                cell.CellValueType += "int";
            }

            //switch (cell.CellValueType)
            //{
            //    case "Boolean":
            //        cell.CellValue = GetCellValue<bool>(data);
            //        break;
            //    case "String":
            //        cell.CellValue = GetCellValue<string>(data);
            //        break;
            //    case "double":
            //        cell.CellValue = GetCellValue<double>(data);
            //        break;
            //    case "float":
            //        cell.CellValue = GetCellValue<float>(data);
            //        break;
            //    case "long":
            //        cell.CellValue = GetCellValue<long>(data);
            //        break;
            //    case "int":
            //        cell.CellValue = GetCellValue<int>(data);
            //        break;
            //}
            cell.CellValue = GetCellValue<string>(data);
            //去除前置后置空格，去除换行符
            cell.CellValue = cell.CellValue.Replace("\n", "").Replace("\r", "").Trim();

            //标识主键
            if (property.IndexOf("=P") >= 0)
            {
                cell.IsPKey = true;
                row.AtKey = cell.CellValue;
            }

            if (property.IndexOf("=AC") >= 0)
            {
                cell.Cellgs = "AC";
            }
            else if (property.IndexOf("=AS") >= 0)
            {
                cell.Cellgs = "AS";
            }
            else
            {
                cell.Cellgs = "ALL";
            }
            row.Cells.Add(cell.CellName, cell);
            return true;
        }

        string GetCellValue<T>(ICell data)
        {
            /*空白的话，根据传入的类型返回默认值*/
            if (data != null && data.CellType != CellType.Blank)
            {
                /*数字类型*/
                if (data.CellType == CellType.Numeric || (data.CellType == CellType.Formula && data.CachedFormulaResultType == CellType.Numeric))
                {
                    return Convert.ChangeType(data.NumericCellValue, typeof(T)).ToString();
                }
                /*字符类型*/
                else if (data.CellType == CellType.String || (data.CellType == CellType.Formula && data.CachedFormulaResultType == CellType.String))
                {
                    if (!string.IsNullOrWhiteSpace(data.StringCellValue.Trim()))
                        return Convert.ChangeType(data.StringCellValue.Trim(), typeof(T)).ToString();
                }
                /*布尔类型*/
                else if (data.CellType == CellType.Boolean || (data.CellType == CellType.Formula && data.CachedFormulaResultType == CellType.Boolean))
                {
                    return Convert.ChangeType(data.BooleanCellValue, typeof(T)).ToString();
                }
                /*默认类型*/
                else
                {
                    if (!string.IsNullOrWhiteSpace(data.ToString().Trim()))
                        return Convert.ChangeType(data.ToString().Trim(), typeof(T)).ToString();
                }
            }
            T t = default(T);
            return t == null ? "" : t.ToString();
        }


        public void Read(string excelPath, Action<string> show)
        {
            try
            {
                using (FileStream fs = File.OpenRead(excelPath))   //打开myxls.xls文件
                {
                    HSSFWorkbook wk = new HSSFWorkbook(fs);   //把xls文件中的数据写入wk中
                    if (wk.NumberOfSheets > 0)
                    {
                        var table = wk.GetSheetAt(0);
                        string _sheetName = this.ReplaceSideLine(table.SheetName).ToLower();
                        show("开始分析文件：" + Path.GetFileName(excelPath) + " 文件数据");
                        if (table != null)
                        {
                            if (table.LastRowNum < 2)
                            {
                                show("文件格式是第一行是字段名，第二行是注释，第三行是数据");
                                return;
                            }
                            IRow rowTitle = table.GetRow(0);  //读取头
                            IRow rowNontes = table.GetRow(1);  //读取配置说明
                            for (int i = 2; i <= table.LastRowNum; i++)
                            {
                                IRow rowData = table.GetRow(i); //读取当前行数据
                                if (rowData != null)
                                {
                                    // 处理如果该行为空值那么忽略
                                    // todo 考虑怎么添加
                                    for (int k = 0; k < rowTitle.LastCellNum; k++)
                                    {
                                        ICell cell2 = rowData.GetCell(k);  //当前表格
                                        if (cell2 != null && !string.IsNullOrWhiteSpace(cell2.ToString())) { goto lab_xx; }
                                    }
                                    return;
                                lab_xx:
                                    ExeclData.ExeclDataRow datarow = new ExeclData.ExeclDataRow();
                                    for (int k = 0; k < rowTitle.LastCellNum; k++)  //LastCellNum 是当前行的总列数
                                    {
                                        try
                                        {
                                            GetExcelPropertyType(rowTitle.GetCell(k), rowNontes.GetCell(k), rowData.GetCell(k), ref datarow);
                                        }
                                        catch (Exception ex)
                                        {
                                            show("文件：" + Path.GetFileName(excelPath) + " 取值错误 第 " + (i + 1) + " 行 " + " 第 " + (k + 1) + " 列 " + ex.ToString());
                                            return;
                                        }
                                    }
                                    if (string.IsNullOrWhiteSpace(datarow.AtKey))
                                    {
                                        show("文件：" + Path.GetFileName(excelPath) + " 缺少主键");
                                        return;
                                    }
                                    if (Rows.ContainsKey(datarow.AtKey))
                                    {
                                        show("文件：" + Path.GetFileName(excelPath) + " 存在重复主键 " + datarow.AtKey + " 第 " + (i + 1) + " 行 ");
                                        return;
                                    }

                                    if (datarow.Cells.Values.Where(l => l.IsPKey).Count() > 1)
                                    {
                                        show("文件：" + Path.GetFileName(excelPath) + " 不支持双主键设置");
                                        return;
                                    }
                                    Rows.Add(datarow.AtKey, datarow);
                                }
                            }
                            SheetName = _sheetName;
                        }
                    }
                }
            }
            catch (Exception)
            {
                show("请确保是2003或者wps创建的excel文件并且处于关闭状态：" + Path.GetFileName(excelPath));
            }
        }

        /// <summary>
        /// 数据行
        /// </summary>
        public class ExeclDataRow
        {
            class ExcelCellComparer : IComparer<ExeclDataCell>
            {
                public int Compare(ExeclDataCell x, ExeclDataCell y)
                {
                    return string.Compare(x.CellName, y.CellName, true);
                }
            }

            /// <summary>
            /// 数据块
            /// </summary>
            public class ExeclDataCell
            {
                /// <summary>
                /// 标识字段的归属 ALL=客户端和服务器通用 AC=表示客户端使用 AS=表示服务使用
                /// </summary>
                public string Cellgs { get; set; }
                /// <summary>
                /// 注释
                /// </summary>
                public string CellNotes { get; set; }

                /// <summary>
                /// Excel节点名称
                /// </summary>
                public string CellName { get; set; }

                /// <summary>
                /// 值
                /// </summary>
                public string CellValue { get; set; }

                /// <summary>
                /// 属性类型 int，float
                /// </summary>
                public string CellValueType { get; set; }

                /// <summary>
                /// 长度，，如字符串类型 在数据需要设置长度 默认 255
                /// </summary>
                public int CellLength { get; set; }

                /// <summary>
                /// 是否是唯一键
                /// </summary>
                public bool IsPKey { get; set; }

                /// <summary>
                /// 关联excel文件
                /// </summary>
                public string CellFileName { get; set; }

                public string ToXmlString(string cellgs, bool is_Not_Null)
                {
                    if (Cellgs.Equals("ALL") || cellgs.Equals("ALL") || Cellgs.Equals(cellgs))
                    {
                        if (is_Not_Null)
                        {
                            if (string.IsNullOrWhiteSpace(CellValue) || "0".Equals(CellValue))
                            {
                                return null;
                            }
                        }
                        return " " + CellName + "=\"" + CellValue + "\"";
                    }
                    return "";
                }

                public string ToMysqlString(string cellgs)
                {
                    StringBuilder sbuilder = new StringBuilder();
                    if (Cellgs.Equals("ALL") || cellgs.Equals("ALL") || Cellgs.Equals(cellgs))
                    {
                        sbuilder.Append(" `" + CellName + "` ");
                        switch (CellValueType.ToLower())
                        {
                            case "int":
                                sbuilder.Append("int ");
                                break;
                            case "boolean":
                                sbuilder.Append(" TINYINT(1) ");
                                break;
                            case "double":
                                sbuilder.Append(" double ");
                                break;
                            case "float":
                                sbuilder.Append(" float ");
                                break;
                            case "string":

                                if (CellLength < 500)
                                {
                                    sbuilder.Append(" VARCHAR(" + CellLength + ") ");
                                }
                                else if (CellLength < 10000)
                                {
                                    sbuilder.Append(" TEXT ");
                                }
                                else
                                {
                                    sbuilder.Append(" LONGTEXT ");
                                }
                                break;
                            case "long":
                                sbuilder.Append(" bigint ");
                                break;
                        }
                        if (IsPKey)
                        {
                            sbuilder.Append(" NOT NULL primary key ");
                        }
                        sbuilder.Append(" COMMENT '" + CellNotes + "'");
                    }
                    return sbuilder.ToString();
                }
            }

            public Dictionary<string, ExeclDataCell> Cells = new Dictionary<string, ExeclDataCell>();


            /// <summary>
            /// 主键，唯一键
            /// </summary>
            public string AtKey { get; set; }

            public string ToXmlString(string cellgs, bool is_Not_Null)
            {
                StringBuilder xmlBuilder = new StringBuilder();
                foreach (var item in Cells)
                {
                    string tmp = item.Value.ToXmlString(cellgs, is_Not_Null);
                    if (!string.IsNullOrWhiteSpace(tmp))
                    {
                        xmlBuilder.Append(tmp);
                    }
                }
                return xmlBuilder.ToString();
            }


            /// <summary>
            /// 生成表头
            /// </summary>
            /// <returns></returns>
            public string ToMysqlString(string cellgs)
            {
                StringBuilder xmlBuilder = new StringBuilder();
                bool isdouhao = false;
                var tmp = Cells.Values.ToArray();
                for (int i = 0; i < tmp.Length; i++)
                {
                    string mysql = tmp[i].ToMysqlString(cellgs);
                    if (!string.IsNullOrWhiteSpace(mysql))
                    {
                        if (isdouhao) { xmlBuilder.AppendLine(","); }
                        else { xmlBuilder.AppendLine(""); }
                        xmlBuilder.Append("  " + mysql);
                        isdouhao = true;
                    }
                }
                return xmlBuilder.ToString();
            }


            public override string ToString()
            {
                return base.ToString();
            }

        }

    }
}
